﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Windows;

namespace DetectingKinectSencor.Tools
{
    class SqliteConnection
    {
        public static DateTime startedCaptureTime { get; set; }

        internal static SQLiteConnection openDBConnection(String databaseName)
        {
            SQLiteConnection databaseConnection;

            try
            {
                // create a new database connection:
                databaseConnection = new SQLiteConnection("Data Source=" + databaseName + ".db;Version=3;FailIfMissing=True;");

                // open the connection:
                databaseConnection.Open();

                return databaseConnection;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        internal static void closeConnection(SQLiteConnection databaseConnection)
        {
            // We are ready, now lets cleanup and close our connection:
            databaseConnection.Close();
        }

        internal static void createTables(SQLiteConnection databaseConnection)
        {
            SQLiteCommand sqliteCommand;

            sqliteCommand = databaseConnection.CreateCommand();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE captured_time (id integer primary key,  capturedTime TEXT, stoppedTime TEXT);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE open_gestures (id integer primary key, capturedTime TEXT, timepoint TEXT, timespanMinute INTEGER, timespanSec REAL);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE hands_below_wasteline (id integer primary key, capturedTime TEXT, timepoint TEXT, timespanMinute INTEGER, timespanSec REAL);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE movement (id integer primary key, capturedTime TEXT, timepoint TEXT, timespanMinute INTEGER, timespanSec REAL);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE looked_away (id integer primary key, capturedTime TEXT, timepoint TEXT, timespanMinute INTEGER, timespanSec REAL);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE slides_video (id integer primary key, capturedTime TEXT, timepoint TEXT, timespanMinute INTEGER, timespanSec REAL);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE slides_picture (id integer primary key, capturedTime TEXT, timepoint TEXT, timespanMinute INTEGER, timespanSec REAL);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE slides_change (id integer primary key, capturedTime TEXT, timepoint TEXT, timespanMinute INTEGER, timespanSec REAL);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();

            // Create the sql query to create the tables
            sqliteCommand.CommandText = "CREATE TABLE oral_communication (id integer primary key, capturedTime TEXT, timepoint TEXT, timespanMinute INTEGER, timespanSec REAL, wordsPerMinute INTEGER);";

            // Execute the sql query
            sqliteCommand.ExecuteNonQuery();
        }



        internal static void deleteDatabase(SQLiteConnection databaseConnection)
        {
            // Create a new SQlite command
            SQLiteCommand sqliteCommand;
            sqliteCommand = databaseConnection.CreateCommand();

            //Delete all tables
            sqliteCommand.CommandText = "DELETE * FROM open_gestures;";
            sqliteCommand.ExecuteNonQuery();

            sqliteCommand.CommandText = "DELETE * FROM hands_below_wasteline;";
            sqliteCommand.ExecuteNonQuery();

            sqliteCommand.CommandText = "DELETE * FROM mvoement;";
            sqliteCommand.ExecuteNonQuery();

            sqliteCommand.CommandText = "DELETE * FROM looked_away;";
            sqliteCommand.ExecuteNonQuery();

            sqliteCommand.CommandText = "DELETE * FROM captured_time;";
            sqliteCommand.ExecuteNonQuery();

            sqliteCommand.CommandText = "DELETE * FROM slides_video;";
            sqliteCommand.ExecuteNonQuery();

            sqliteCommand.CommandText = "DELETE * FROM slides_picture;";
            sqliteCommand.ExecuteNonQuery();

            sqliteCommand.CommandText = "DELETE * FROM slides_change;";
            sqliteCommand.ExecuteNonQuery();
        }

        
        internal static void saveCaptureTime(SQLiteConnection databaseConnection, DateTime stoppedCaptureTime)
        {
            // Create a new SQlite command
            SQLiteCommand sqliteCommand;
            sqliteCommand = databaseConnection.CreateCommand();

            // Saved the detection into the sqlite DB
            sqliteCommand.CommandText = "INSERT INTO captured_time (capturedTime, stoppedTime) VALUES ('" + startedCaptureTime + "', '" + stoppedCaptureTime + "');";
            sqliteCommand.ExecuteNonQuery();
        }



        

        internal static string createDB(string databaseName)
        {
            SQLiteConnection databaseConnection;

            try
            {
                // create a new database connection:
                databaseConnection = new SQLiteConnection("Data Source=" + databaseName + ".db;Version=3;FailIfMissing=True;");

                // open the connection:
                databaseConnection.Open();

                return "true";
            }
            catch (Exception ex)
            {
                if (ex.Message.Equals("unable to open database file"))
                {

                    // create a new database connection:
                    databaseConnection = new SQLiteConnection("Data Source=" + databaseName + ".db;Version=3;New=True;Compress=True;");

                    // open the connection:
                    databaseConnection.Open();

                    // Create the tables in the DB
                    SqliteConnection.createTables(databaseConnection);

                    //Close the connection
                    SqliteConnection.closeConnection(databaseConnection);

                    return "true";
                }
                return ex.Message;
            }
        }
    }
}
